﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;

namespace produccion.clases
{
    public class CLSDETPPRP
    {
        MDOgeneral mod = new MDOgeneral();
        string codprv_dpp, codmat_dpp, codcol_dpp;
        long nroped_dpp;
        double canpro_dpp, canrec_dpp, candes_dpp, cosmtp_dpp;

        public string CODPRV_DPP
        {
            get { return codprv_dpp; }
            set { codprv_dpp = value; }
        }

        public long NROPED_DPP
        {
            get { return nroped_dpp; }
            set { nroped_dpp = value; }
        }

        public string CODMAT_DPP
        {
            get { return codmat_dpp; }
            set { codmat_dpp = value; }
        }

        public string CODCOL_DPP
        {
            get { return codcol_dpp; }
            set {codcol_dpp=value;}
        }

        public double CANPRO_DPP
        {
            get { return canpro_dpp; }
            set { canpro_dpp = value; }
        }

        public double CANREC_DPP
        {
            get { return canrec_dpp; }
            set { canrec_dpp = value; }
        }

        public double CANDES_DPP
        {
            get { return candes_dpp; }
            set { candes_dpp = value; }
        }
        public double COSMTP_DPP
        {
            get { return cosmtp_dpp; }
            set { cosmtp_dpp = value; }
        }

        public void LimpiarValores()
        {
            CODPRV_DPP = "";
            NROPED_DPP = 0;
            CODMAT_DPP = "";
            CODCOL_DPP = "";
            CANPRO_DPP = 0;
            CANREC_DPP = 0;
            CANDES_DPP = 0;
            COSMTP_DPP = 0;
        }

        public DataTable Imprimir()
        {
            try
            {
                string query = "select CODPRV_DPP, NROPED_DPP, CODMAT_DPP, CODCOL_DPP, NOMMAT_MAT, NOMCOL_COL, (CANPRO_DPP - CANDES_DPP) CANPRO_DPP, CANREC_DPP, CANDES_DPP, COSMTP_DPP, CANPRO_DPP * COSMTP_DPP AS COSTOTOTAL from detpprp inner join maemat on codmat_dpp=codmat_mat inner join maecolp on codcol_dpp=codcol_col where codprv_dpp='" + CODPRV_DPP + "' and nroped_dpp=" + NROPED_DPP + " and (canpro_dpp - candes_dpp) > 0";
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataTable ImprimirConsultaOrdenCompra(bool Pendiente)
        {
            try
            {
                string query;
                if (Pendiente == true)
                {
                    query = "select CODPRV_DPP, NROPED_DPP, CODMAT_DPP, CODCOL_DPP, NOMMAT_MAT, NOMCOL_COL, CANPRO_DPP, CANREC_DPP, CANDES_DPP from detpprp inner join encpprp on codprv_dpp=codprv_epp and nroped_dpp=nroped_epp inner join maemat on codmat_dpp=codmat_mat inner join maecolp on codcol_dpp=codcol_col where codprv_dpp='" + CODPRV_DPP + "' and ESTPED_EPP='P'";
                }
                else
                {
                    query = "select CODPRV_DPP, NROPED_DPP, CODMAT_DPP, CODCOL_DPP, NOMMAT_MAT, NOMCOL_COL, CANPRO_DPP, CANREC_DPP, CANDES_DPP from detpprp inner join encpprp on codprv_dpp=codprv_epp and nroped_dpp=nroped_epp inner join maemat on codmat_dpp=codmat_mat inner join maecolp on codcol_dpp=codcol_col where codprv_dpp='" + CODPRV_DPP + "'";
                }
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataTable ImprimirConsultaOrdenCompraMaterial()
        {
            try
            {
                string query;
                query = "select CODPRV_DPP, NROPED_DPP, CODMAT_DPP, CODCOL_DPP, NOMMAT_MAT, NOMCOL_COL, CANPRO_DPP, CANREC_DPP, CANDES_DPP from detpprp inner join encpprp on codprv_dpp=codprv_epp and nroped_dpp=nroped_epp inner join maemat on codmat_dpp=codmat_mat inner join maecolp on codcol_dpp=codcol_col where codmat_dpp='" + CODMAT_DPP + "'";
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }


        public DataTable ConsultaOrdenCompra()
        {
            try
            {
                string query = "select codmat_dpp || codcol_dpp as Codigo, rtrim(nommat_mat) || ' ' || rtrim(nomcol_col) as Descripcion, (eximtp_mtp - cansep_mtp) as Existencia, canpro_dpp as Cant, canrec_dpp as Recib, candes_dpp as Desca, 0 as De_mas, (canpro_dpp - canrec_dpp - candes_dpp) as Pend from detpprp inner join maemat on codmat_dpp=codmat_mat inner join maecolp on codcol_dpp=codcol_col inner join maemtp on codmat_dpp=codmat_mtp and codcol_dpp=codcol_mtp where codprv_dpp='" + CODPRV_DPP + "' and nroped_dpp=" + NROPED_DPP;
                DataTable dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Consultar()
        {
            try
            {
                //string query = "select codmat_dpp || codcol_dpp as Codigo, nommat_mat || ' ' || nomcol_col as Descripcion, (eximtp_mtp - cansep_mtp) as Existencia, canpro_dpp as Cant, canrec_dpp as Recib, candes_dpp as Desca, 0 as ADesca,(canpro_dpp - canrec_dpp - candes_dpp) as Pend, 0 as Costo from detpprp inner join maemat on codmat_dpp=codmat_mat inner join maecolp on codcol_dpp=codcol_col inner join maemtp on codmat_dpp=codmat_mtp and codcol_dpp=codcol_mtp where codprv_dpp='" + CODPRV_DPP + "' and nroped_dpp=" + NROPED_DPP;
                string query = "select codmat_dpp || codcol_dpp as Codigo, TRIM(nommat_mat) || ' ' || trim(nomcol_col) || ' (' || trim(nomuni_eun) || ')' as Descripcion, NVL((eximtp_mtp - cansep_mtp),0) as Existencia, canpro_dpp as Cant, canrec_dpp as Recib, candes_dpp as Desca, 0 as ADesca,(canpro_dpp - canrec_dpp - candes_dpp) as Pend, COSMTP_DPP as Costo from detpprp inner join maemat on codmat_dpp=codmat_mat inner join maecolp on codcol_dpp=codcol_col left join maemtp on codmat_dpp=codmat_mtp and codcol_dpp=codcol_mtp inner join encunip on coduni_eun = unicom_mat where codprv_dpp='" + CODPRV_DPP + "' and nroped_dpp = " + NROPED_DPP;
                DataTable dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    query = "Select '' as Codigo, '' as Descripcion, '0' as Existencia, '0' as Cant, '0' as Recib, '0' as Desca, '0' as ADesca, '0' as Pend, '0' as Costo from detpprp where ROWNUM<=1";
                    dt = mod.consulta(query);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable PedidosCambiarDescartar()
        {
            try
            {
                string query = "select nroped_epp as Pedido,codprv_epp as Codigo,nomprv_prv as Proveedor,fecped_epp as Fecha, (canpro_dpp - canrec_dpp - candes_dpp) as Pendiente from Encpprp inner join maeprvp on codprv_epp=codprv_prv inner join detpprp on nroped_epp=nroped_dpp and codprv_epp=codprv_dpp where estped_epp='P' and canpro_dpp - canrec_dpp - candes_dpp > 0  and  codmat_dpp='" + CODMAT_DPP + "' and codcol_dpp='" + CODCOL_DPP + "' order by codprv_epp, nroped_epp";
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int Actualizar()
        {
            try
            {
                //string query = "update detpprp set candes_dpp = candes_dpp + " + CANDES_DPP + ", canpro_dpp=" + CANPRO_DPP + ", canrec_dpp=" + CANREC_DPP + " where codprv_dpp='" + CODPRV_DPP + "' and nroped_dpp=" + NROPED_DPP + " and codmat_dpp='" + CODMAT_DPP + "' and codcol_dpp='" + CODCOL_DPP + "' and cosmtp_dpp = " + COSMTP_DPP + " ";
                string query = "update detpprp set candes_dpp = candes_dpp + " + CANDES_DPP + ", canpro_dpp=" + CANPRO_DPP + " where codprv_dpp='" + CODPRV_DPP + "' and nroped_dpp=" + NROPED_DPP + " and codmat_dpp='" + CODMAT_DPP + "' and codcol_dpp='" + CODCOL_DPP + "' ";
                return mod.comando(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Boolean ExisteRegistro()
        {
            try
            {
                string query = "select codprv_dpp from detpprp where codprv_dpp='" + CODPRV_DPP + "' and nroped_dpp=" + NROPED_DPP + " and codmat_dpp='" + CODMAT_DPP + "' and codcol_dpp='" + CODCOL_DPP + "'";
                if (mod.consulta(query).Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int Insertar()
        {
            try
            {
                string query = "insert into detpprp(codprv_dpp, nroped_dpp, codmat_dpp, codcol_dpp, canpro_dpp, canrec_dpp, candes_dpp, cosmtp_dpp) values ('" + CODPRV_DPP + "'," + NROPED_DPP + ",'" + CODMAT_DPP + "','" + CODCOL_DPP + "'," + CANPRO_DPP + "," + CANREC_DPP + "," + CANDES_DPP + ", " + COSMTP_DPP + ")";
                return mod.comando(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string UniComMP()
        {
            try
            {
                DataTable dt=new DataTable();
                string query = "select unicom_mat from maemat where codmat_mat='" + CODMAT_DPP + "'";
                dt= mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows[0][0].ToString();
                }
                return "";
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ConsultaOrdenCompraMaterial()
        {
            try
            {
                string query = "select codprv_epp as codigo, nomprv_prv as nombre, nroped_epp as nro_pedido, estped_epp AS ESTADO, codmat_dpp || codcol_dpp as materia_prima, nommat_mat as descripcion, sum(canpro_dpp) as pedido, sum(canpro_dpp - canrec_dpp - candes_dpp) as pendiente, fecped_epp as fechapedido, fecent_epp as fechaentrega from detpprp inner join encpprp on codprv_dpp=codprv_epp and nroped_dpp=nroped_epp inner join maeprvp on codprv_epp=codprv_prv inner join maemat on codmat_dpp=codmat_mat where codmat_dpp='" + CODMAT_DPP + "' group by codprv_epp, nomprv_prv, nroped_epp, estped_epp, codmat_dpp || codcol_dpp, nommat_mat, fecped_epp, fecent_epp  order by codprv_epp, nroped_epp desc";
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ConsultaOrdenCompraMaterialColor()
        {
            try
            {
                string query = "select codprv_epp as codigo, nomprv_prv as nombre, nroped_epp as nro_pedido, estped_epp AS ESTADO, codmat_dpp || codcol_dpp as materia_prima, nommat_mat as descripcion, sum(canpro_dpp) as pedido, sum(canpro_dpp - canrec_dpp - candes_dpp) as pendiente, fecped_epp as fechapedido, fecent_epp as fechaentrega from detpprp inner join encpprp on codprv_dpp=codprv_epp and nroped_dpp=nroped_epp inner join maeprvp on codprv_epp=codprv_prv inner join maemat on codmat_dpp=codmat_mat where codmat_dpp='" + CODMAT_DPP + "' and codcol_dpp= '" + CODCOL_DPP + "' group by codprv_epp, nomprv_prv, nroped_epp, estped_epp, codmat_dpp || codcol_dpp, nommat_mat, fecped_epp, fecent_epp order by codprv_epp, nroped_epp desc";
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
